
package vinterprojekt.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.ArrayList;
import vinterprojekt.entity.Book;

import static vinterprojekt.util.ErrorHandler.error;

/**
 * DBController er ansvarlig for alt database-tilgang. Via denne klasse kan der søges, opdateres, indsættes og slettes.
 * @author jacob
 */
public class DBController
{
    private static DBController instance = null;
    private static String dbPath = null;

    // konstanter til felt-navne i Book-tabellen
    private static enum BookFields
    {
        Title
        {
            @Override public String toString() { return "Title"; }
        },

        YearOfPublication
        {
            @Override public String toString() { return "YearOfPublication"; }
        },

        EditionNumber
        {
            @Override public String toString() { return "EditionNumber"; }
        },

        PublisherName
        {
            @Override public String toString() { return "PublisherName"; }
        },

        ISBN
        {
            @Override public String toString() { return "ISBN"; }
        },

        AuthorName
        {
            @Override public String toString() { return "AuthorName"; }
        }
    };


    private Connection connection = null;


    public static void configure(String dbPath)
    {
        DBController.dbPath = dbPath;
    }

    public static DBController getInstance()
    {
        if (instance == null)
        {
            try
            {
                instance = new DBController();
            }

            catch(Exception e)
            {
                vinterprojekt.util.ErrorHandler.error(e, true);
            }

        }

        return instance;
    }

    /**
     * Opretter forbindelse til databasen én gang
     */
    private DBController() throws Exception
    {
        Class.forName("org.sqlite.JDBC");

        connection = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
        connection.setAutoCommit(true);
    }

    public Book bookSearchIsbn(String isbn)
    {   
        if (isbn == null || isbn.isEmpty()) error(new IllegalArgumentException("ISBN er enten tom eller null"), true);

        try
        {
            PreparedStatement ps = connection.prepareStatement("SELECT * FROM Book WHERE isbn=?");
            ps.setString(1, isbn);

            ResultSet rs = ps.executeQuery();

            // TODO: hvorfor har Book alle disse attributter når vi ikke gemmer dem i databasen?
            return rs.next() ? rsToBook(rs) : null;
        }

        catch (Exception e)
        {
            error(e, true);
            return null; // vil aldrig ske, da error() stopper programmet, men det ved javac ikke noget om
        }
    }

    public int bookGetTotalCount()
    {
        String sql = "SELECT * FROM book";

        try
        {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            int count = 0;
            while (rs.next()) count += 1;

            return count;
        }

        catch (Exception e)
        {
            error(e, true);
            return 0; // sker aldrig
        }
        
    }

    public Book[] bookGetAll()
    {
        List<Book> results = new ArrayList<Book>();

        String sql = "SELECT * FROM Book";

        try
        {
            PreparedStatement st = connection.prepareStatement(sql);
            ResultSet rs = st.executeQuery();

            while (rs.next())
            {
                Book next = rsToBook(rs);
                results.add(next);
            }

            rs.close();
        }

        catch (Exception e)
        {
            error(e, true);
        }


        return results.toArray(new Book[0]);
    }

    /**
     *
     * @param author
     * @param title
     * @return Array af Books eller null hvis søgning ikke gav resultat
     */
    public Book[] bookSearch(String author, String title)
    {
        List<Book> results = new ArrayList<Book>();

        if (author.isEmpty() && title.isEmpty()) error(new IllegalArgumentException("Både author og title er tomme"), true);
        
        // har både author og title en værdi?
        if (author != null && title != null)
        {
            author = author.toLowerCase();
            title = title.toLowerCase();

            try
            {
                String sql = "SELECT * FROM book WHERE authorname LIKE ? AND title LIKE ?";
                PreparedStatement prep = connection.prepareStatement(sql);

                prep.setString(1, "%" + author + "%");
                prep.setString(2, "%" + title + "%");

                ResultSet rs = prep.executeQuery();
                while (rs.next())
                {
                    Book book = rsToBook(rs);
                    results.add(book);
                }

            }

            catch (Exception e)
            {
                error(e, true);
            }
            
        }

         // vi har kun title at søge efter
        else if (author == null)
        {
            title = title.toLowerCase();
            
            try
            {
                String sql = "SELECT * FROM book WHERE title LIKE ?";
                
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setString(1, "%" + title + "%");

                ResultSet rs = ps.executeQuery();

                while (rs.next())
                {
                    Book book = rsToBook(rs);
                    results.add(book);
                }
                
             }

             catch(Exception e)
             {
                 error(e, true);
             }
        }

        // vi har kun author at søge efter
        else
        {
             author = author.toLowerCase();

             try
             {
                String sql = "SELECT * FROM book WHERE authorname LIKE ?";

                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setString(1, "%" + author + "%");

                ResultSet rs = ps.executeQuery();

                while (rs.next())
                {
                    Book book = rsToBook(rs);
                    results.add(book);
                }

             }

             catch(Exception e)
             {
                 error(e, true);
             }
        }

        return results.toArray(new Book[0]);
    }

    public void bookSaveNew(Book book) throws Exception
    {
        String isbn = book.getISBN();
        String title = book.getTitle().toLowerCase();
        String edition = book.getEdition();
        String author = book.getAuthor().toLowerCase();
        String publisher = book.getPublisher().toLowerCase();
        String year = book.getYearOfPublication();

        // først, check om bogens ISBN allerede er i databasen
        if (bookSearchIsbn(isbn) != null) throw new Exception("ISBN " + isbn + " findes allerede i databasen");

        try
        {
            String sql = "";
            PreparedStatement ps = null;

            // er forfatteren oprettet?
            // note: vores bookSearch(author, null) metode dur ikke her, da den søger via LIKE %parameter% - vi skal bruge noget mere eksakt
            if (!isFieldValueCreated("Author", "AuthorName", author))
            {
                sql = "INSERT INTO Author(authorname) VALUES (?)";

                ps = connection.prepareStatement(sql);
                ps.setString(1, author);

                ps.execute();
            }


            // er edition oprettet?
            if (!isFieldValueCreated("Edition", "EditionNumber", edition))
            {
                sql = "INSERT INTO Edition (EditionNumber) VALUES(?)";

                ps = connection.prepareStatement(sql);
                ps.setString(1, edition);

                ps.execute();
            }

            
            // er publisher oprettet?
            if (!isFieldValueCreated("Publisher", "PublisherName", publisher))
            {
                sql = "INSERT INTO Publisher (PublisherName) VALUES(?)";

                ps = connection.prepareStatement(sql);
                ps.setString(1, publisher);

                ps.execute();
            }

            // så blev det year
            if (!isFieldValueCreated("Year", "YearOfPublication", year))
            {
                sql = "INSERT INTO Year (YearOfPublication) VALUES(?)";

                ps = connection.prepareStatement(sql);
                ps.setString(1, year);

                ps.execute();
            }


            // endelig - gem selve Book instansen!
            String fieldNames = "(Isbn, Title, EditionNumber, AuthorName, PublisherName, YearOfPublication)";
            sql = "INSERT INTO Book " + fieldNames + " VALUES(?, ?, ?, ?, ?, ?)";

            ps = connection.prepareStatement(sql);
            ps.setString(1, isbn);
            ps.setString(2, title);
            ps.setString(3, edition);
            ps.setString(4, author);
            ps.setString(5, publisher);
            ps.setString(6, year);

            ps.execute();
        }

        catch(java.sql.SQLException sqle)
        {
            error(sqle, true);
        }
    }

    public Book bookUpdate(Book book, String title, String edition, String authorName, String publisherName, String yearOfPublication)
    {        
        try
        {
            String update = "Title=?, EditionNumber=?, AuthorName=?, publisherName=?, YearOfPublication=?";
            String sql = "UPDATE Book SET " + update + " WHERE ISBN=?";

            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, title);
            ps.setString(2, edition);
            ps.setString(3, authorName);
            ps.setString(4, publisherName);
            ps.setString(5, yearOfPublication);
            ps.setString(6, book.getISBN());

            ps.executeUpdate();
        }

        catch(Exception e)
        {
            error(e, true);
        }

            String isbnSuffix = ""; // ?
            String remark = ""; // ?
            boolean avail = true; // ?

            return new Book(title, yearOfPublication, edition, publisherName, book.getISBN(), isbnSuffix, remark, authorName, avail);
    }

    /** bruges til testen */
    public void bookDelete(Book book)
    {
        try
        {
            String sql = "DELETE FROM Book WHERE Isbn=?";
            
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, book.getISBN());
            if (ps.executeUpdate() != 1) error(new Exception("Bog ikke slettet"), true);
        }

        catch (java.sql.SQLException sqle)
        {
            error(sqle, true);
        }
    }

    public void close()
    {
        DBController.instance = null; // næste kald til getInstance vil resultere i en ny forbindelse

        try
        {
            connection.close();
        }

        catch(Exception e)
        {
            error(e, true);
        }
        
    }

    // TODO: brug BookController når den er brugbar
    private Book rsToBook(ResultSet rs)
    {
        try
        {
            String title = rs.getString(BookFields.Title.toString());
            String yearOfPublication = rs.getString(BookFields.YearOfPublication.toString());
            String edition = rs.getString(BookFields.EditionNumber.toString());
            String publisher = rs.getString(BookFields.PublisherName.toString());
            String isbn = rs.getString(BookFields.ISBN.toString());
            String isbnSuffix = ""; // ?
            String remark = ""; // ?
            String author = rs.getString(BookFields.AuthorName.toString());
            boolean avail = true; // ?

            return new Book(title, yearOfPublication, edition, publisher, isbn, isbnSuffix, remark, author, avail);
        }

        catch (Exception e)
        {
            error(e, true);
            return null; // vil aldrig kaldes, men gør javac glad
        }

    }

    private boolean isFieldValueCreated(String table, String field, String value)
    {
        try
        {
            String sql = "SELECT * FROM " + table + " WHERE " + field + "=?";
            
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, value);
          
            ResultSet rs = ps.executeQuery();
            return rs.next();
        }

        catch (Exception e)
        {
            error(e, true);
            return false; // igen: javac brok
        }
    }
}
